package common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Blob;
import java.io.File;
import java.io.Reader;

/**
 * Class to hold some SQL Query that is used in multiple places.
 * 
 * It provides access to two Singletons, CatAndPoints and QuestionManager.
 */
public class SQLCall
{
	private static CatAndPoints cap = null;
	private static QuestionManager qManager = null;
	
	/**
	 * Clears SQLCall's memory. This should be done after every
	 * completed generation.
	 * 
	 * Tomcat keeps an instance of the program, if we do not
	 * clear this, then we won't get updates for future
	 * generations.
	 */
	public static void clear() {
		cap = null;
		qManager = null;
	}
	
	/**
	 * Get the next value to the sequence passed in
	 * 
	 * @param conn
	 *            The database connection
	 * @param sequence
	 *            The name of the sequence
	 * @return The next value in the sequence
	 */
	public static int getNextSeq(Connection conn, String sequence) throws SQLException
	{
		int index = -1;
		
		String query = "SELECT " + sequence + ".nextval from dual";
		PreparedStatement pst = conn.prepareStatement(query);
		ResultSet rset = pst.executeQuery();
		rset.next();
		index = rset.getInt(1);
		pst.close();
	
		return index;
	}

	/**
	 * Grab a list of all the question type that question should be generated
	 * for.
	 * 
	 * @param conn
	 *            A database connection
	 * @return CatAndPoints object of all current type(category) of question available
	 */
	public static CatAndPoints getCategoryList(Connection conn) throws SQLException
	{
		// If an instance already exists, return it.
		if(cap != null)
			return cap;

		cap = new CatAndPoints();
		Statement quesType = conn.createStatement();
		ResultSet rset = quesType
				.executeQuery("SELECT type_code, points, type_description FROM QUESTION_TYPE");
		while (rset.next())
		{
			cap.add(rset.getInt("type_code"), rset.getInt("points"), 
					rset.getString("type_description"));
		}
		quesType.close();
		rset.close();
		
		return cap;
	}
	
	/**
	 * Constructs a QuestionManager and fills it with required data.
	 * 
	 * @param conn A connection to the database.
	 * @return The question manager which handles the questions of an exam.
	 * @throws SQLException 
	 */
	public static QuestionManager getQuestionManager(Connection conn) 
	throws SQLException
	{
		// If an instance already exists, return it.
		if(qManager != null)
			return qManager;
		
		qManager = new QuestionManager();
		
		// Make sure that cap is not null;
		getCategoryList(conn);
		
		for(int cat : cap) {
			PreparedStatement quesType = conn.prepareStatement(
					"SELECT question_id, points "
					+ "FROM QUESTION_CONTENT INNER JOIN QUESTION "
					+ "ON QUESTION.question_content_id = "
					+ "QUESTION_CONTENT.question_content_id "
					+ "WHERE question_type_code = ?");
			quesType.setInt(1, cat);
			ResultSet r = quesType.executeQuery();
			
			try
			{
				while (r.next())
				{
					// Add details to the questionManager
					qManager.add(new QuestionDetails(cat,
							r.getInt("question_id"), r.getInt("points")));
				}
			} catch (Exception e)
			{
				e.printStackTrace();
			}
			
			r.close();// close the ResultSet
			quesType.close();// close Statement
		}
	
		qManager.finish();
		return qManager;
	}

	//This method queries the database to find input output pairs. This is used by the testing function.
	public static String[] getTestFiles(Connection conn, int question_id, int q_version, int pair_number, int studentORgrader) throws SQLException 
	//if studentORgrader == 0, we are looking for student i/o. if studentORgrader == 1 we are looking for grader i/o
	{
		int kinID; //this will help us determine whether it's an input, output, or source file.
		String[] fileContents = new String[3];
		String temp;
		int input, output;
		if(studentORgrader == 0)// if we are pulling a student i/o pair
		{
			//grab the corresponding kinID.
			input = 32;
			output = 64;
		}
		else //otherwise, we have a grader i/o pair
		{
			input = 33;
			output = 65;
		}
		
		PreparedStatement getFileInfo = conn.prepareStatement
		(
				"SELECT kin, source_file, io_pair " +
				"FROM source_file " +
				"WHERE question_id = " + question_id + " AND q_version = " + q_version + " AND ((io_pair = " + pair_number + " AND (kin = " + input + " OR kin = "+ output + ")) OR kin = 3)");
			System.out.println(
				"SELECT kin, source_file, io_pair " +
				"FROM source_file " +
				"WHERE question_id = " + question_id + " AND q_version = " + q_version + " AND ((io_pair = " + pair_number + " AND (kin == " + input + " OR kin = "+ output + ")) OR kin = 3)");
			//catch results
			ResultSet r = getFileInfo.executeQuery();
			//String sourceFile;
			try
			{
				int count = 1;
				while(r.next())
				{
					//sourceFile = r.getString("source_file_name");
					//System.out.println("Resultset record " + ++count + " sourcefilename: " + sourceFile );
					kinID = r.getInt("kin");
					System.out.println("The kin id is: " + kinID);
					
					Blob blob;
					blob = r.getBlob("source_file");
					byte[] blobData = blob.getBytes(1, (int)blob.length());
					temp = new String(blobData);
					
					//System.out.println("The result is: " + result);
					
					if(kinID == 3) // it's a source file... outReaders[0] is for source
					{
						//outReaders[0] = clob.getCharacterStream();
						fileContents[0] = temp;
						System.out.println("Sourcefile " + fileContents[0].toString());
 					}
					else if(kinID == 32 || kinID == 65)// it's an input file... outReaders[1] is for source
					{
						//outReaders[1] = clob.getCharacterStream();
						fileContents[1] = temp;
						System.out.println("Input " + fileContents[1].toString());
					}
					else if(kinID == 33 || kinID == 64)// its an output file... outReaders[2]
					{
						//outReaders[2] = clob.getCharacterStream();
						fileContents[2] = temp;
						System.out.println("Output " + fileContents[2].toString());
					}
					else // otherwise we have a BIG problem. We should never see another kinID
					{
						System.out.println("There is a serious problem");
					}
				}// end while
			}// try
			catch(Exception e)
			{
				e.printStackTrace();
			}// end catch
	return fileContents;
	}// end method

}

